﻿using Cocbook_DAL;
using Cocbook_DTO;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Cocbook_DAL
{
    public class Project_BookDAL
    {
        public bool CreateProject_Book(Project_Book pb)
        {
            DB.SetQuery("INSERT INTO Project_Book VALUES(@pID, @bID, @copy, @discount, @tableNumber, @render)");
            DB.AddParameters("pID", pb.Project.ProjectID);
            DB.AddParameters("bID", pb.Book.BookID);
            DB.AddParameters("copy", pb.Copy);
            DB.AddParameters("discount", pb.Discount);
            DB.AddParameters("tableNumber", pb.TableNumber);
            DB.AddParameters("render", pb.Render);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.CreateProject_Book", e.ToString());
                return false;
            }
        }

        public bool UpdateProject_Book(Project_Book pb)
        {
            DB.SetQuery("UPDATE Project_Book SET Copy = @copy, Discount = @discount, TableNumber = @tablenum, "+
                        "Render = @render WHERE (ProjectID = @pID) and (BookID = @bID)");
            DB.AddParameters("pID", pb.Project.ProjectID);
            DB.AddParameters("bID", pb.Book.BookID);
            DB.AddParameters("copy", pb.Copy);
            DB.AddParameters("discount", pb.Discount);
            DB.AddParameters("tablenum", pb.TableNumber);
            DB.AddParameters("render", pb.Render);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.UpdateProject_Book", e.ToString());
                return false;
            }
        }

        public bool DeleteProject_Book(int pID, int bID)
        {
            DB.SetQuery("DELETE FROM Project_Book WHERE (BookID = @bid) and (ProjectID = @pid)");
            DB.AddParameters("pID", pID);
            DB.AddParameters("bID", bID);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.DeleteProject_Book", e.ToString());
                return false;
            }
        }

        public Project_Book GetProject_BookByID(int pid, int bid)
        {
            try
            {
                DB.SetQuery("SELECT ProjectID , BookID , Copy , Discount, TableNumber, Render FROM Project_Book WHERE BookID = @bid and ProjectID = @pid");
                DB.AddParameters("bid", bid);
                DB.AddParameters("pid", pid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Project_Book p = new Project_Book();
                if (llo.Count > 0)
                {
                    p.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(llo[0][0]));
                    p.Book = (new BookDAL()).GetBookById(Convert.ToInt16(llo[0][1]));
                    p.Copy = Convert.ToInt16(llo[0][2]);
                    p.Discount = Convert.ToDouble(llo[0][3]);
                    p.TableNumber = Convert.ToInt16(llo[0][4]);
                    p.Render = Convert.ToBoolean(llo[0][5]);
                }
                else
                    "".Substring(2);
                return p;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetProject_BookByID", e.ToString());
                return null;
            }
        }

        public List<ManageWareHouseView> GetAllWareHouseByProjectIDByFilter(int pid = 0,string title = "", string isbn = "",bool isChecked = true, string order = "",bool desc = true)
        {
            try
            {
                string sqlQuery = (@"SELECT copy.TableNumber, b.ISBN,b.Title,b.Price,copy.Discount,copy.Copy AS Import, ISNULL(Sold.Sold,0) AS Sold , (copy.Copy- ISNULL(Sold.Sold,0) ) as Inventory
                              FROM
	                                (SELECT        dbo.Bill_Book.BookID, SUM(dbo.Bill_Book.Copy) AS Sold
		                                FROM            dbo.Bill INNER JOIN
								                                 dbo.Bill_Book ON dbo.Bill.BillID = dbo.Bill_Book.BillID
		                                WHERE        (dbo.Bill.ProjectID = @pid)
		                                GROUP BY dbo.Bill_Book.BookID) AS Sold RIGHT OUTER JOIN
	                                (SELECT pb.BookID, pb.TableNumber,pb.Discount ,pb.Copy 
	                                FROM Project_Book pb
	                                WHERE ProjectID = @pid) AS copy ON sold.BookID = copy.BookID INNER JOIN
	                                Book b on b.BookID = copy.BookID
                              WHERE (Title LIKE @title) AND (CAST(b.ISBN as nvarchar(20)) LIKE @isbn) 
                                    AND (copy.TableNumber = @isChecked OR @isChecked = 1)
                                ORDER BY Inventory");
                if (order.Length > 0)
                {
                    switch (order.ToLower())
                    { 
                        case "title":
                            sqlQuery += " ORDER BY b.Title";
                            break;
                        case "table":
                            sqlQuery += " ORDER BY copy.TableNumber";
                            break;
                    }
                    if (desc)
                    {
                        sqlQuery += " DESC";
                    }
                }
                DB.SetQuery(sqlQuery);
                if (isChecked)
                { 
                    DB.AddParameters("isChecked",0);
                }
                else
                {
                    DB.AddParameters("isChecked",1);
                }
                DB.AddParameters("pid", pid);
                DB.AddParameters("isbn", isbn + "%");
                DB.AddParameters("title", "%" + title + "%");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<ManageWareHouseView> result = new List<ManageWareHouseView>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        ManageWareHouseView whv = new ManageWareHouseView();
                        whv.TableNo = Convert.ToInt16(lo[0]);
                        whv.ISBN = Convert.ToInt64(lo[1]);
                        whv.Title = lo[2].ToString();
                        whv.Price = Convert.ToInt32(lo[3]);
                        whv.Discount = Convert.ToDouble(lo[4]);
                        whv.Import = Convert.ToInt32(lo[5]);
                        whv.Sold = Convert.ToInt32(lo[6]);
                        whv.Inventory = Convert.ToInt32(lo[7]);
                        result.Add(whv);
                    }
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetAllWareHouseByProjectID", e.ToString());
                return null;
            }
        }

        public List<BookReportView> GetBookReportByProjectID(int pid = 0)
        {
            try
            {
                string sqlQuery = (@"SELECT b.ISBN,b.Title,b.Type,b.Price,b.Issuer,copy.Copy AS Import, ISNULL(Sold.Sold,0) AS Sold
                              FROM
	                                (SELECT        dbo.Bill_Book.BookID, SUM(dbo.Bill_Book.Copy) AS Sold
		                                FROM            dbo.Bill INNER JOIN
								                                 dbo.Bill_Book ON dbo.Bill.BillID = dbo.Bill_Book.BillID
		                                WHERE        (dbo.Bill.ProjectID = @pid)
		                                GROUP BY dbo.Bill_Book.BookID) AS Sold RIGHT OUTER JOIN
	                                (SELECT pb.BookID, pb.TableNumber,pb.Discount ,pb.Copy 
	                                FROM Project_Book pb
	                                WHERE ProjectID = @pid) AS copy ON sold.BookID = copy.BookID INNER JOIN
	                                Book b on b.BookID = copy.BookID");
                
                DB.SetQuery(sqlQuery);
                DB.AddParameters("pid", pid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<BookReportView> result = new List<BookReportView>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        BookReportView whv = new BookReportView(Convert.ToInt64(lo[0]),lo[1].ToString(),lo[2].ToString(),Convert.ToInt32(lo[3]),lo[4].ToString(),Convert.ToInt32(lo[5]),Convert.ToInt32(lo[6]));
                        result.Add(whv);
                    }
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetBookReportByProjectID", e.ToString());
                return null;
            }
        }


        public List<Project_Book> GetProject_BookByBookID(int bID)
        {
            try
            {
                DB.SetQuery("SELECT ProjectID , BookID , Copy , Discount, TableNumber, Render FROM Project_Book WHERE BookID = @id");
                DB.AddParameters("id", bID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Project_Book> project = new List<Project_Book>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        Project_Book p = new Project_Book();
                        p.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[0]));
                        p.Book = (new BookDAL()).GetBookById(Convert.ToInt16(lo[1]));
                        p.Copy = Convert.ToInt16(lo[2]);
                        p.Discount = Convert.ToDouble(lo[3]);
                        p.TableNumber = Convert.ToInt16(lo[4]);
                        p.Render = Convert.ToBoolean(lo[5]);
                        project.Add(p);
                    }
                }
                else
                    "".Substring(2);
                return project;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetProject_BookByBookID", e.ToString());
                return null;
            }
        }

        public int GetQuatityInWareHouseBookByBookID(int pID, int bid)
        {
            try
            {
                DB.SetQuery(@"  SELECT (copy.Copy- ISNULL(Sold.Sold,0) ) as abc
                                FROM
                                (SELECT        SUM(dbo.Bill_Book.Copy) AS Sold
                                FROM            dbo.Bill INNER JOIN
                                                         dbo.Bill_Book ON dbo.Bill.BillID = dbo.Bill_Book.BillID
                                WHERE        (dbo.Bill.ProjectID = @pid)  AND (dbo.Bill_Book.BookID = @bid)) AS Sold, 
                                (SELECT Copy FROM Project_Book WHERE ProjectID = @pid AND BookID = @bid) AS copy");
                DB.AddParameters("pid", pID);
                DB.AddParameters("bid", bid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                int result = 0;
                if (llo.Count>0)
                {
                    result = Convert.ToInt16(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetQuatityOfSoldBookByBookID", e.ToString());
                return 0;
            }
        }

        public List<Book> GetAllBookByProjectIDAndBookTitle(int pID,string title)
        {
            try
            {
                DB.SetQuery("SELECT [Book].[BookID], ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Project_Book JOIN Book ON (Book.BookID = Project_Book.BookID) and (Project_Book.ProjectID = @id) and ((Book.Title like N'%"+title+"%')OR(N'"+title+"' = ''))");
                DB.AddParameters("id", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Book> book = new List<Book>();
                Book b;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        b = new Book();
                        b.BookID = Convert.ToInt16(lo[0]);
                        b.ISBN = Convert.ToInt64(lo[1]);
                        b.Title = Convert.ToString(lo[2]);
                        b.Type = Convert.ToString(lo[3]);
                        b.Publisher = Convert.ToString(lo[4]);
                        b.Issuer = Convert.ToString(lo[5]);
                        if (lo[6] == null)
                        {
                            b.Size = "";
                        }
                        else
                        {
                            b.Size = Convert.ToString(lo[6]);
                        }
                        if (lo[7] == System.DBNull.Value)
                        {
                            b.Page = "";
                        }
                        else
                        {
                            b.Page = Convert.ToString(lo[7]);
                        }
                        if (lo[8] == null)
                        {
                            b.Genre = "";
                        }
                        else
                        {
                            b.Genre = lo[8].ToString();
                        }
                        b.Price = Convert.ToDouble(lo[9]);
                        if (lo[10] == System.DBNull.Value)
                        {
                            b.Weigh = 0;
                        }
                        else
                        {
                            b.Weigh = Convert.ToDouble(lo[10]);
                        }
                        book.Add(b);
                    }
                }
                else
                    "".Substring(2);
                return book;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetAllBookByProjectIDAndBookTitle", e.ToString());
                return null;
            }
        }

        public List<Project_Book> GetAllProject_BookByProjectIDAndBookTitle(int pID, string title)
        {
            try
            {
                DB.SetQuery("SELECT [Book].[BookID], ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Project_Book JOIN Book ON (Book.BookID = Project_Book.BookID) and (Project_Book.ProjectID = @id) and (Book.Title like @title)");
                DB.AddParameters("id", pID);
                DB.AddParameters("title", "%" + title + "%");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Project_Book> project = new List<Project_Book>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        Project_Book p = new Project_Book();
                        p.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[0]));
                        p.Book = (new BookDAL()).GetBookById(Convert.ToInt16(lo[1]));
                        p.Copy = Convert.ToInt16(lo[2]);
                        p.Discount = Convert.ToDouble(lo[3]);
                        p.TableNumber = Convert.ToInt16(lo[4]);
                        p.Render = Convert.ToBoolean(lo[5]);
                        project.Add(p);
                    }
                }
                else
                    "".Substring(2);
                return project;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetAllProject_BookByProjectIDAndBookTitle", e.ToString());
                return null;
            }
        }

        public Int32 GetTotalBookSoldByProjectIDAndBookID(int pID,int bid)
        {
            try
            {
                DB.SetQuery(@"SELECT        SUM(dbo.Bill_Book.Copy) AS Expr1
                                FROM            dbo.Bill INNER JOIN
                                                         dbo.Bill_Book ON dbo.Bill.BillID = dbo.Bill_Book.BillID
                                WHERE        (dbo.Bill.ProjectID = @pid) AND (dbo.Bill_Book.BookID = @bid)");
                DB.AddParameters("pid", pID);
                DB.AddParameters("bid", bid);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt32(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_Book.GetTotalBookSoldByProjectID", e.ToString());
                return 0;
            }
        }

        public Int32 GetTotalBookSoldByProjectID(int pID)
        {
            try
            {
                DB.SetQuery(@"SELECT SUM(BookSold.Sold) AS Sold
                                FROM
                                (SELECT S0.BookID, (ISNULL(S1.BillSold, 0) + ISNULL(S2.OrderSold, 0)) AS Sold
                                FROM
	                                ((SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid)) AS S0
	                                LEFT JOIN
	                                (SELECT b.BookID, SUM(bb.Copy) AS BillSold
	                                FROM Book b, Bill_Book bb, Bill bi
	                                WHERE b.BookID = bb.BookID
		                                AND bi.BillID = bb.BillID
		                                AND bi.ProjectID = @pid
		                                AND (b.BookID IN
		                                (SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid))
	                                GROUP BY b.BookID) AS S1 ON S0.BookID = S1.BookID
	                                LEFT JOIN
	                                (SELECT b.BookID, SUM(ob.Copy) AS OrderSold
	                                FROM Book b, Order_Book ob, [Order] o
	                                WHERE b.BookID = ob.BookID
		                                AND o.OrderID = ob.OrderID
		                                AND o.ProjectID = @pid
		                                AND (b.BookID IN
		                                (SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid))
	                                GROUP BY b.BookID) AS S2 ON S2.BookID = S0.BookID) AS BookSold");
                DB.AddParameters("pid", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt32(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_Book.GetTotalBookSoldByProjectID", e.ToString());
                return 0;
            }
        }

        public Int32 GetBookInputByProjectID(int pID)
        {
            try
            {
                DB.SetQuery(@"SELECT COUNT(Project_Book.BookID) AS QBook FROM Project_Book WHERE Project_Book.ProjectID = @pid ");
                DB.AddParameters("pid", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt32(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetBookInputByProjectID", e.ToString());
                return 0;
            }
        }

        public Int32 GetTotalBookInputByProjectID(int pID)
        {
            try
            {
                DB.SetQuery(@"SELECT SUM(Project_Book.Copy) AS QBook FROM Project_Book WHERE Project_Book.ProjectID = @pid");
                DB.AddParameters("pid", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt32(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetTotalBookInputByProjectID", e.ToString());
                return 0;
            }
        }

        public Int32 GetBookSoldByProjectID(int pID)
        {
            try
            {
                DB.SetQuery(@"SELECT COUNT(BookSold.BookID) AS Sold
                                FROM
                                (SELECT S0.BookID, (ISNULL(S1.BillSold, 0) + ISNULL(S2.OrderSold, 0)) AS Sold
                                FROM
	                                ((SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid)) AS S0
	                                LEFT JOIN
	                                (SELECT b.BookID, SUM(bb.Copy) AS BillSold
	                                FROM Book b, Bill_Book bb, Bill bi
	                                WHERE b.BookID = bb.BookID
		                                AND bi.BillID = bb.BillID
		                                AND bi.ProjectID = @pid
		                                AND (b.BookID IN
		                                (SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid))
	                                GROUP BY b.BookID) AS S1 ON S0.BookID = S1.BookID
	                                LEFT JOIN
	                                (SELECT b.BookID, SUM(ob.Copy) AS OrderSold
	                                FROM Book b, Order_Book ob, [Order] o
	                                WHERE b.BookID = ob.BookID
		                                AND o.OrderID = ob.OrderID
		                                AND o.ProjectID = @pid
		                                AND (b.BookID IN
		                                (SELECT DISTINCT pb.BookID 
		                                FROM Project_Book pb
		                                WHERE pb.ProjectID = @pid))
	                                GROUP BY b.BookID) AS S2 ON S2.BookID = S0.BookID) AS BookSold 
                            WHERE BookSold.Sold>0");
                DB.AddParameters("pid", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Int32 result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt32(llo[0][0]);
                }
                else
                    "".Substring(2);
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetBookSoldByProjectID", e.ToString());
                return 0;
            }
        }


        public List<Book> GetAllBookByProjectID(int pID)
        {
            try
            {
                DB.SetQuery("SELECT [Book].[BookID], ISBN,Title,Type,Publisher,Issuer,Size,Page,Genre,Price,Weigh FROM Project_Book JOIN Book ON (Book.BookID = Project_Book.BookID) and (Project_Book.ProjectID = @id)");
                DB.AddParameters("id", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Book> book = new List<Book>();
                Book b;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        b = new Book();
                        b.BookID = Convert.ToInt16(lo[0]);
                        b.ISBN = Convert.ToInt64(lo[1]);
                        b.Title = Convert.ToString(lo[2]);
                        b.Type = Convert.ToString(lo[3]);
                        b.Publisher = Convert.ToString(lo[4]);
                        b.Issuer = Convert.ToString(lo[5]);
                        if (lo[6] == null)
                        {
                            b.Size = "";
                        }
                        else
                        {
                            b.Size = Convert.ToString(lo[6]);
                        }
                        if (lo[7] == System.DBNull.Value)
                        {
                            b.Page = "";
                        }
                        else
                        {
                            b.Page = Convert.ToString(lo[7]);
                        }
                        if (lo[8] == null)
                        {
                            b.Genre = "";
                        }
                        else
                        {
                            b.Genre = lo[8].ToString();
                        }
                        b.Price = Convert.ToDouble(lo[9]);
                        if (lo[10] == System.DBNull.Value)
                        {
                            b.Weigh = 0;
                        }
                        else
                        {
                            b.Weigh = Convert.ToDouble(lo[10]);
                        }
                        book.Add(b);
                    }
                }
                else
                    "".Substring(2);
                return book;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetAllBookByProjectID", e.ToString());
                return null;
            }
        }

        public List<Project_Book> GetProject_BookByProjectID(int pID)
        {
            try
            {
                DB.SetQuery("SELECT ProjectID , BookID , Copy , Discount, TableNumber, Render FROM Project_Book WHERE ProjectID = @id");
                DB.AddParameters("id", pID);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Project_Book> project = new List<Project_Book>();
                Project_Book p;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        p = new Project_Book();
                        p.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[0]));
                        p.Book = (new BookDAL()).GetBookById(Convert.ToInt16(lo[1]));
                        p.Copy = Convert.ToInt16(lo[2]);
                        p.Discount = Convert.ToDouble(lo[3]);
                        p.TableNumber = Convert.ToInt16(lo[4]);
                        p.Render = Convert.ToBoolean(lo[5]);
                        project.Add(p);
                    }
                }
                else
                    "".Substring(2);
                return project;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetProject_BookByProjectID", e.ToString());
                return null;
            }
        }

        public List<Project_Book> GetAllProject_Book()
        {
            try
            {
                DB.SetQuery("SELECT ProjectID , BookID , Copy , Discount, TableNumber, Render FROM Project_Book");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Project_Book> project = new List<Project_Book>();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        Project_Book p = new Project_Book();
                        p.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[0]));
                        p.Book = (new BookDAL()).GetBookById(Convert.ToInt16(lo[1]));
                        p.Copy = Convert.ToInt16(lo[2]);
                        p.Discount = Convert.ToDouble(lo[3]);
                        p.TableNumber = Convert.ToInt16(lo[4]);
                        p.Render = Convert.ToBoolean(lo[5]);
                        project.Add(p);
                    }
                }
                else
                    "".Substring(2);
                return project;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("CocBookDAL.Project_BookDAL.GetAllProject_Book", e.ToString());
                return null;
            }
        }
    }
}
